package com.sigmatrix.h5.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.sigmatrix.h5.dao.DisDealerDao;
import com.sigmatrix.h5.dto.DisDealersData;
import com.sigmatrix.h5.entity.DisDealer;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.CommonUtils;
import com.sigmatrix.h5.utils.Cons;
import com.sigmatrix.h5.utils.Constants;
import com.sigmatrix.h5.utils.Utils;
import com.sigmatrix.sc.jdbc.common.dao.DaoImpl;

/**
 * 
 *@ClassName: DistributionDealersDaoImpl
 *@Description: 经销商表
 *@author MH
 *@date 2017年9月27日 上午10:59:29
 */
@Repository
public class DisDealerDaoImpl extends DaoImpl<DisDealer, Integer> implements DisDealerDao{

	@Override
	public DisDealer findFirstLevelParent(Integer id) {
		StringBuffer sql = new StringBuffer();
		sql.append("with o as(");
		sql.append(" select * from ").append(tableName()).append(" a where a._id = ? ");
		sql.append("union all ");
		sql.append("select b.* from ").append(tableName()).append(" b,o where b._id = o._parent_id ");
		sql.append(") select * from o where o._level = 1");
		List<DisDealer> list = query(sql.toString(), id);
		return CommonUtils.isEmpty(list)?null:list.get(0);
	}

	@Override
	public List<DisDealer> findParents(Integer id) {
		return findParents(id, null);
	}
	@Override
	public List<DisDealer> findParents(String dealerCode) {
		return findParents(null, dealerCode);
	}
	private List<DisDealer> findParents(Integer id,String dealerCode){
		List<Object> values = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("with o as(");
		sql.append(" select * from ").append(tableName()).append(" a where 1 = 1 ");
		if(CommonUtils.isEmpty(id)){
			sql.append(" and a._code = ? ");
			values.add(dealerCode);
		}else{
			sql.append(" and a._id = ? ");
			values.add(id);
		}
		sql.append("union all ");
		sql.append("select b.* from ").append(tableName()).append(" b,o where b._id = o._parent_id ");
		sql.append(") select * from o ");
		return query(sql.toString(), values.toArray());
	}
	
	
	public static void main(String[] args) {
		StringBuffer sql = new StringBuffer();
		sql.append("with o as(");
		sql.append(" select * from ").append("biaoxxx").append(" a where 1 = 1 ");
		sql.append(" and a._code = ? ");
		sql.append("union all ");
		sql.append("select b.* from ").append("biaoxxx").append(" b,o where b._id = o._parent_id ");
		sql.append(") select * from o ");
		
		System.out.println(sql);
	}
	
	@Override
	public List<DisDealer> findByIds(Object... ids) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(tableName());
		sql.append(" where _id in (");
		for (int i = 0; i < ids.length; i++) {
			sql.append("?,");
		}
		sql.replace(sql.length()-1, sql.length(), ")");
		return query(sql.toString(), ids);
	}

	@Override
	public int selectCountByDealerName(String dealerName) {
		
		return selectCount(dealerName, null);
	}

	private int selectCount(String name,String contact){
		StringBuffer sql = new StringBuffer();
		List<String> args = new ArrayList<String>();
		sql.append("select count(1) from ").append(tableName());
		sql.append(" where _status <> ").append(Constants.STATUS_DELETE);
		if(StringUtils.isNotBlank(name)){
			sql.append(" and _name = ? ");
			args.add(name);
		}
		if(StringUtils.isNoneBlank(contact)){
			sql.append(" and _contact = ? ");
			args.add(contact);
		}
		return queryForObject(sql.toString(), int.class, args.toArray());
	}

	@Override
	public DisDealer findByPromotionCode(String promotionCode) {
		DisDealer dealer = new DisDealer();
		dealer.setPromotionCode(promotionCode);
		List<DisDealer> list = select(dealer);
		//注册时一不小心保存了推广码，这些数据是无效的，只有基础数据同步以后才是有效的数据
		if(CommonUtils.isNotEmpty(list)){
			for (final DisDealer _dealer : list) {
				if(CommonUtils.isNotEmpty(_dealer.getBasicModuleId())){
					return _dealer;
				}
			}
		}
		return null;
	}
	
	@Override
	public List<DisDealer> findParentsByPromotionCode(String promotionCode) {
		final StringBuffer sql = new StringBuffer();
		sql.append("WITH m as(");
		sql.append(" select * from ").append(tableName()).append(" a where a._promotion_code = ?");
		sql.append(" UNION ALL");
		sql.append(" select b.* from ").append(tableName()).append(" b,m where b._id = m._parent_id");
		sql.append(" )select * from m");
		logger.info("sql="+sql.toString());
		logger.info("param="+promotionCode);
		return jdbcTemplate.query(sql.toString(), new DefaultRowMapper(), promotionCode);
	}

	@Override
	public List<DisDealer> findChildren(Integer id) {
		final StringBuffer sql = new StringBuffer();
		sql.append("WITH m as(");
		sql.append(" select * from ").append(tableName()).append(" a where a._id = ?");
		sql.append(" UNION ALL");
		sql.append(" select b.* from ").append(tableName()).append(" b,m where b._parent_id = m._id");
		sql.append(" )select * from m");
		logger.info("sql="+sql.toString());
		logger.info("param="+id);
		return jdbcTemplate.query(sql.toString(), new DefaultRowMapper(), id);
	}

	@Override
	public List<Integer> findFirstChildIdAnSelf(Integer dealerId) {
		final StringBuffer sql = new StringBuffer();
		sql.append(" select  _id  from ").append(tableName()).append(" a where a._parent_id = ");
		sql.append(dealerId);
		sql.append(" UNION select ");
		sql.append(dealerId);
		logger.info("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString(), Integer.class);
	}
	
	
	
	
	/**
	 * 分销app合并分支开始-------------------------------------
	 */
	

	private static final String TABLE_NAME = "_distribution_dealers";

	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.sc.distribution.app.inter.dao.DealersDao#findById(java.lang.Integer)
	 *@author youjun
	 *@date 2017年7月15日 上午11:47:11
	 */
	@Override
	public DisDealer findById(final Integer id, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _id = ").append(id);
		logger.info(sql.toString());
		final List<DisDealer> list = jdbcTemplate.query(sql.toString(), new DealersRowMapper());
		if (Utils.isNotEmpty(list)) {
			return list.get(0);
		}else{
			return null;
		}
	}

	/**
	 * (non-Javadoc)
	 *@Title: findListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.distribution.app.inter.dao.DealersDao#findListByCondition(com.sigmatrix.h5.entity.DisDealer)
	 *@author youjun
	 *@date 2017年7月15日 上午11:47:20
	 */
	@Override
	public List<DisDealer> findListByCondition(final DisDealer dealers, final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(enterpriseCode, TABLE_NAME))
				.append(" WHERE _status <> ?");
		final List<Object> paramList = new ArrayList<Object>();
		paramList.add(Cons.STATUS_DELETE);
		sql.append(assemblyWhereConditon(dealers, paramList, true));
		try {
			logger.info(sql.toString());
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new DealersRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**(non-Javadoc)
	 *@Title: findPartColumnListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.distribution.app.inter.dao.DealersDao#findPartColumnListByCondition(com.sigmatrix.sc.distribution.app.inter.web.vo.DealersVo)
	 *@author youjun
	 *@date 2017年6月27日 上午11:31:14
	 */
	@Override
	public List<DisDealersData> findPartColumnListByCondition(final DisDealer dealers, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT _id, _name, _code, _level, _parent_id, _assist_code FROM ");
		sql.append(getTname(enterpriseCode, TABLE_NAME));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		sql.append(assemblyWhereConditon(dealers, paramList, true));
		logger.info(sql.toString());
		try {
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new RowMapper<DisDealersData>() {

				@Override
				public DisDealersData mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					final DisDealersData dealers = new DisDealersData();
					dealers.setDealerId(resultSet.getInt("_id"));
					dealers.setDealerName(resultSet.getString("_name"));
					dealers.setDealerCode(resultSet.getString("_code"));
					dealers.setLevel(resultSet.getShort("_level"));
					dealers.setParentId(resultSet.getInt("_parent_id"));
					dealers.setDealerModel(resultSet.getString("_assist_code"));
					return dealers;
				}
			});
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	/**
	 *@Title: assemblyWhereConditon
	 *@Description: 根据条件组装sql的where条件部分
	 *@return Object
	 *@author youjun
	 *@date 2017年6月27日 下午1:46:00
	 *@param dealersVo
	 *@param paramList
	 *@param isExistWhereCondition
	 * @throws DaoException 
	 */
	private String assemblyWhereConditon(final DisDealer dealers, final List<Object> paramList, final boolean isExistWhereCondition) throws DaoException {
		
		if(paramList==null) {
			throw new DaoException("传递的参数列表不能为null");
		}
		StringBuilder whereSql = new StringBuilder();
		if(!isExistWhereCondition) {
			whereSql.append(" WHERE 1=1");
		}
		if(dealers!=null) {
			if(dealers.getLevel()!=null) {
				whereSql.append(" AND _level = ?");
				paramList.add(dealers.getLevel());
			}
			if(dealers.getParentId()!=null) {
				whereSql.append(" AND _parent_id = ?");
				paramList.add(dealers.getParentId());
			}
		}
		return whereSql.toString();
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findByIdPart
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.DisDealersDao#findByIdPart(java.lang.Integer, java.lang.String)
	 *@author 
	 *@date 2017年9月8日 下午4:03:13
	 */
	public DisDealersData findByIdPart(final Integer id, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _id = ").append(id).append(" AND _status = ").append(Cons.STATUS_ENABLE);
		logger.info(sql.toString());
		final List<DisDealersData> list = jdbcTemplate.query(sql.toString(), new DealersDataRowMapper());
		if (Utils.isNotEmpty(list)) {
			return list.get(0);
		}else{
			return null;
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findChildPart
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.DisDealersDao#findChildPart(java.lang.Integer, java.lang.String)
	 *@author 
	 *@date 2017年9月8日 下午4:03:17
	 */
	public List<DisDealersData> findChildPart(final Integer dealerId, final String eseCode) throws DaoException {
		try {
			final String sql = this.getRecursionSql(eseCode,dealerId,Cons.IS_CHILD);
			return jdbcTemplate.query(sql, new DealersDataRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 *@Title: getRecursionSql
	 *@Description: 递归查询sql
	 *@return String
	 *@date 2017年8月12日 下午6:05:51
	 *@param eseCode
	 *@param dealerId
	 *@param isFatherChild 查询上级还是下级标识
	 */
	private String getRecursionSql(final String eseCode,final Integer dealerId,final Integer isFatherChild){
		//查询返回字段
		String columns1 = " _id, _name, _code, _level, _parent_id, _assist_code ";
		String columns2 = " a._id, a._name, a._code, a._level, a._parent_id, a._assist_code ";
		
		final String tableName = getTname(eseCode, TABLE_NAME);
		StringBuilder sql = new StringBuilder()
		.append("WITH tree AS ")
		.append(" ( ")
		.append(" SELECT ").append(columns1)//查询返回字段
		.append(" FROM ")
		.append(tableName)
		.append(" WHERE _id = ").append(dealerId)
		.append(" UNION ALL")
		.append(" SELECT ").append(columns2)//查询返回字段
		.append(" FROM ").append(tableName).append(" a ")
		.append(" INNER JOIN tree ");
		if(Cons.IS_FATHER==isFatherChild){
			logger.info("查询父级 {}",isFatherChild);
			sql.append(" ON tree._parent_id = a._id");
		}else{
			logger.info("查询子集 {}",isFatherChild);
			sql.append(" ON a._parent_id = tree._id");
		}
		sql.append(" AND a._status = ").append(Cons.STATUS_ENABLE);
		sql.append(" )")
		.append(" SELECT * FROM tree ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 *@ClassName: DealersRowMapper
	 *@Description: 分装数据库返回实体
	 *@date 2017年8月12日 下午5:20:02
	 */
	public class DealersDataRowMapper implements RowMapper<DisDealersData> {
		public DisDealersData mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
			final DisDealersData dealers = new DisDealersData();
			dealers.setDealerId(resultSet.getInt("_id"));
			dealers.setDealerName(resultSet.getString("_name"));
			dealers.setDealerCode(resultSet.getString("_code"));
			dealers.setLevel(resultSet.getShort("_level"));
			dealers.setParentId(resultSet.getInt("_parent_id"));
			dealers.setDealerModel(resultSet.getString("_assist_code"));
			return dealers;
		}
	}

	/**
	 * @ClassName: DealersRowMapper
	 * @Description: 封装返回的实体
	 * @author youjun @date 2017-06-24 17:30:21
	 */
	public class DealersRowMapper implements RowMapper<DisDealer> {
		public DisDealer mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
			final DisDealer dealers = new DisDealer();
			dealers.setId(resultSet.getInt("_id"));
			dealers.setName(resultSet.getString("_name"));
			dealers.setCode(resultSet.getString("_code"));
			dealers.setLevel(resultSet.getShort("_level"));
			dealers.setParentId(resultSet.getInt("_parent_id"));
			dealers.setLicenseCode(resultSet.getString("_license_code"));
			dealers.setContact(resultSet.getString("_contact"));
			dealers.setFixedPhoneNum(resultSet.getString("_fixed_phone_num"));
			dealers.setCellPhoneNum(resultSet.getString("_cell_phone_num"));
			dealers.setSalesRegion(resultSet.getString("_sales_region"));
			dealers.setAddress(resultSet.getString("_address"));
			dealers.setEmail(resultSet.getString("_email"));
			dealers.setCreatorId(resultSet.getInt("_creator_id"));
			dealers.setCreateTime(resultSet.getTimestamp("_create_time"));
			dealers.setUpdateId(resultSet.getInt("_update_id"));
			dealers.setUpdateTime(resultSet.getTimestamp("_update_time"));
			dealers.setDisableId(resultSet.getInt("_disable_id"));
			dealers.setDisableTime(resultSet.getTimestamp("_disable_time"));
			dealers.setEnableId(resultSet.getInt("_enable_id"));
			dealers.setEnableTime(resultSet.getTimestamp("_enable_time"));
			dealers.setStatus(resultSet.getShort("_status"));
			dealers.setRemarks(resultSet.getString("_remarks"));
			dealers.setAssistCode(resultSet.getString("_assist_code"));
			return dealers;
		}
	}

	@Override
	public List<DisDealersData> findChildDealerPart(Integer parendId, String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _parent_id = ").append(parendId).append(" AND _status = ").append(Cons.STATUS_ENABLE);
		logger.info(sql.toString());
		final List<DisDealersData> list = jdbcTemplate.query(sql.toString(), new DealersDataRowMapper());
		return list;
	}
	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.DisDealersDao#findById(java.lang.String, java.lang.String)
	 *@author gaomingkai
	 *@date 2017年12月26日 下午5:56:24
	 */
	@Override
	public DisDealer findByCode(String code, String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(enterpriseCode, TABLE_NAME))
				.append(" WHERE _status = ").append(Cons.STATUS_ENABLE)
				.append(" AND _code = ").append("'"+code+"'");
		logger.info(sql.toString());
		final List<DisDealer> list = jdbcTemplate.query(sql.toString(), new DealersRowMapper());
		if (Utils.isNotEmpty(list)) {
			return list.get(0);
		}else{
			return null;
		}
	}
	
	/**是否为分表策略,为true,表名加上传递的企业code*/
	public static final boolean ISSUBMETER = true;
	
	/**
	 *@Title: getEntTableName
	 *@Description: 根据表策略(分库/表)获得表名
	 *@return String
	 *@date 2017年7月13日 上午11:52:48
	 *@param eseCode
	 *@param tName
	 */
	protected String getTname(final String eseCode, final String tName) {
		
		final StringBuilder sb = new StringBuilder();
		if(ISSUBMETER) { // 分表
			if(Utils.isNotEmpty(eseCode)){//企业code不为空
				sb.append("_").append(eseCode);
			}
		}
		sb.append(tName);
		return sb.toString();
	}
	
	/**
	 * 分销app合并分支结束-------------------------------------
	 */
	
}
